package dao.person;

import domain.Person;
import util.DButils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class PersonDapImpl implements PersonDao{
    /**
     * 新增报名信息
     * @param
     * @return int
     * @author 夏添
     * @date 2020/7/23 9:25
     */
    public int CreatPerson(String name, String sex, int age, String BM, String phone, String idcard, String filename, int isTeam) throws Exception{
        Date now = new Date();
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");//可以方便地修改日期格式


        String hehe = dateFormat.format( now );
        String sql = "insert into person (name,sex,age,BM,phone,idcard,filename,isTeam,creatime) values('"+
                    name+"','"+sex+"',"+age+",'"+BM+"','"+phone+"','"+idcard+"','"+filename+"',"+isTeam+",'"+hehe+"')";
        Connection con = DButils.getConn();
        Statement st = con.createStatement();
        int i = st.executeUpdate(sql);
        DButils.close(st,con);
        return i;
    }

    /**
     * 统计项目人员
     * @param
     * @return int
     * @author 夏添
     * @date 2020/7/23 9:25
     */
    public int countEvent(String pro) throws Exception{
        String sql = "select count(*) from person where BM='"+pro+"'";
        Connection conn = DButils.getConn();
        Statement st =conn.createStatement();
        ResultSet rs = st.executeQuery(sql);
        int count = 0;
        while(rs.next()){
            count = rs.getInt(1);
        }
        DButils.close(rs,st,conn);
        return count;
    }

    /**
     * 获取明细列表
     * @param pro
     * @return List<User>
     * @author 夏添
     * @date 2020/9/23 16:03
     */
    public List<Person> getPersonList(String pro) throws Exception{
        String sql="select * from person where BM='"+pro+"' order by id desc";
        List<Person> list= new ArrayList<>();
        Connection conn = DButils.getConn();
        Statement st =conn.createStatement();
        ResultSet rs = st.executeQuery(sql);
        int id = 0;
        while (rs.next()){
            Person person = new Person();
            id++;
            person.setId(id);
            person.setName(rs.getString("name"));
            person.setSex(rs.getString("sex"));
            person.setBM(rs.getString("BM"));
            person.setAge(rs.getInt("age"));
            person.setPhone(rs.getString("phone"));
            person.setIdcard(rs.getString("idcard"));
            person.setFilename(rs.getString("filename"));
            person.setIsTeam(rs.getInt("isTeam"));
            person.setStatevalue(rs.getInt("statevalue"));
            list.add(person);
        }
        DButils.close(rs,st,conn);
        return list;
    }
}
